/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ig.ejb.metrica.da.editor.dao;

import ig.ejb.metrica.da.editor.dao.abs.DAO;
import ig.ejb.metrica.da.session.editor.dto.ColumnaDTO;
import java.util.List;

/**
 *
 * @author jsanchez
 */
public class ColumnaDao extends DAO {

    @Override
    public Object buscar(Integer id) throws Exception {
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT ID, ID_TABLA, NOMBRE, ALIAS, SELECCION FROM COLUMNA WHERE ID = ?");
            prep.setInt(1, id.intValue());
            resul = prep.executeQuery();
            Object dto = null;
            if (resul.next()) {
                dto = new ColumnaDTO();
                getDatos(resul, dto);
            }
            return dto;
        } catch (ClassNotFoundException ex) {
            throw new Exception(ex);
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }
    public Object buscar(String nombre, Integer idTabla) throws Exception {
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT ID, ID_TABLA, NOMBRE, ALIAS, SELECCION FROM COLUMNA WHERE ID_TABLA = ? AND NOMBRE = ?");
            prep.setInt(1, idTabla);
            prep.setString(2, nombre);
            resul = prep.executeQuery();
            Object dto = null;
            if (resul.next()) {
                dto = new ColumnaDTO();
                getDatos(resul, dto);
            }
            return dto;
        } catch (ClassNotFoundException ex) {
            throw new Exception(ex);
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }

    @Override
    public List buscarTodos() throws Exception {
        List c = new java.util.ArrayList();
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT ID, ID_TABLA, NOMBRE, ALIAS, SELECCION FROM COLUMNA");
            resul = prep.executeQuery();
            while (resul.next()) {
                c.add(getDatos(resul, new ColumnaDTO()));
            }
            return c;
        } catch (ClassNotFoundException ex) {
            throw new Exception(ex);
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }
    public List buscarTodosPorTabla(int idTabla) throws Exception {
        List c = new java.util.ArrayList();
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT ID, ID_TABLA, NOMBRE, ALIAS, SELECCION FROM COLUMNA WHERE ID_TABLA = ?");            
            prep.setInt(1, idTabla);
            resul = prep.executeQuery();
            int index=1;
            while (resul.next()) {
                c.add(getDatos(resul, new ColumnaDTO(index++)));
            }
            return c;
        } catch (ClassNotFoundException ex) {
            throw new Exception(ex);
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }

    @Override
    public Integer guardar(Object obj) throws Exception {
        ColumnaDTO dto = (ColumnaDTO) obj;
        try {
            getConnection();
            if (dto.getId() == null) {
                prep = connection.prepareStatement("INSERT INTO COLUMNA(ID_TABLA, NOMBRE, ALIAS, SELECCION, ID) VALUES(?, ?, ?, ?, ?)");
                dto.setId(getMaximoId("COLUMNA"));
            } else {
                prep = connection.prepareStatement("UPDATE COLUMNA SET ID_TABLA = ?, NOMBRE = ?, ALIAS = ?, SELECCION = ? WHERE ID = ?");
            }
            setDatos(prep, dto,"ID_TABLA, NOMBRE, ALIAS, SELECCION, ID");
            prep.executeUpdate();
            return dto.getId();
        } catch (ClassNotFoundException ex) {
            throw new Exception(ex);
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } catch (Throwable ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }

    @Override
    public void eliminar(Integer id) throws Exception {
        try {
            getConnection();
            prep = connection.prepareStatement("DELETE FROM COLUMNA WHERE ID = ?");
            prep.setInt(1, id.intValue());
            prep.executeUpdate();
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
    }

    @Override
    public Object existe(Object obj) throws Exception {
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT NOMBRE FROM COLUMNA WHERE NOMBRE = ? AND ID_TABLA=?");
            if(obj instanceof ColumnaDTO){
                prep.setString(1, ((ColumnaDTO) obj).getNombre().trim());
                prep.setInt(2, ((ColumnaDTO) obj).getId_Tabla());
            }
            resul = prep.executeQuery();
            if (resul.next()) {
                return true;
            }
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
        return false;
    }

    public boolean seleccionada(ColumnaDTO obj) throws Exception {
        try {
            getConnection();
            prep = connection.prepareStatement("SELECT SELECCION FROM COLUMNA WHERE NOMBRE = ? AND ID_TABLA=?");
            if(obj instanceof ColumnaDTO){
                prep.setString(1, ((ColumnaDTO) obj).getNombre().trim());
                prep.setInt(2, ((ColumnaDTO) obj).getId_Tabla());
            }
            resul = prep.executeQuery();
            if (resul.next()) {
                return resul.getInt(1)==1;
            }
        } catch (java.sql.SQLException ex) {
            throw new Exception(ex);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            closeAll();
        }
        return false;
    }

}
